package com.css.fxfzdzzh.modules.mapManagement.repository;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzdzzh.base.dict.repository.entity.DictEntity;
import com.css.fxfzdzzh.constants.FxfzConstants;
import com.css.fxfzdzzh.constants.YNEnum;
import com.css.fxfzdzzh.modules.mapManagement.entity.Map;
import com.css.fxfzdzzh.modules.mapManagement.param.MapQueryParam;
import com.css.fxfzdzzh.modules.mapManagement.vo.MapAttachment;
import com.css.fxfzdzzh.modules.mapManagement.vo.MapViewVO;
import com.css.fxfzdzzh.modules.taskManage.entity.TaskEntity;
import com.css.fxfzdzzh.util.CreateGson;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import com.css.fxfzdzzh.web.PlatformSessionContext;
import com.css.fxfzdzzh.zcpt.sys.entity.SUser;
import com.css.fxfzdzzh.zcpt.sys.service.SUserService;
import com.css.fxfzdzzh.zcpt.sys.service.SysAreaService;
import com.css.fxfzdzzh.zcpt.sys.service.SysRoleService;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;

/**
 * @author Dbj
 * @date 2023年05月16日  10:23
 */
@Repository
public class MapRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Resource
    SUserService sUserService;
    @Resource
    SysRoleService sysRoleService;
    @Resource
    SysAreaService sysAreaService;

    public void saveMap(Map map) {
    String sql =
        "insert into dzzh_map("
            + "dm_id,"
            + "map_name,"
            + "map_desc,"
            + "map_level1_category,"
            + "map_level2_category,"
            + "attachment_id,"
            + "district,"
            + "upload_user_id,"
            + "upload_user_name,"
            + "upload_time,del_flag,num,province,city,area) values(:dmId, :mapName, :mapDesc, :mapLevel1Category, :mapLevel2Category, :attachmentId, :district , "
            + ":uploadUserId, :uploadUserName, :uploadTime,:delFlag,:num,:province,:city,:area) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(map);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    public java.util.Map<String, Object> mapPage(MapQueryParam param, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("");
        StringBuilder countSql = new StringBuilder("");
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<java.util.Map> roleMaps = JSONObject.parseArray(role, java.util.Map.class);
        for (java.util.Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE)) {
            //国家级管理员 查看所有
            sql.append("select * from dzzh_map where del_flag='0' ");
            countSql.append("select count(*) from dzzh_map where del_flag='0' ");
        }else if (roles.contains(FxfzConstants.DZZH_COUN_YW_ROLE)) {
            //国家级业务人员可以查看图件分类为“地表破裂带评估-基础数据“”地表破裂带评估-评估结果”的
            sql.append("select * from dzzh_map where del_flag='0' and map_level1_category in ('8','9')");
            countSql.append("select count(*) from dzzh_map where del_flag='0' and map_level1_category in ('8','9')");
        }else if (roles.contains(FxfzConstants.DZZH_PRI_GL_ROLE)) {
            String areaName = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            //省管理员
            sql.append("select * from dzzh_map where del_flag='0' and map_level1_category not in ('8','9') and district like '%").append(areaName).append("%'");
            countSql.append("select count(*) from dzzh_map where del_flag='0' and map_level1_category not in ('8','9') and district like '%").append(areaName).append("%'");
        }else{
            //其他只能查看自己上传的
            sql.append("select * from dzzh_map where del_flag='0' and upload_user_id ='").append(userId).append("'");
            countSql.append("select count(*) from dzzh_map where del_flag='0' and upload_user_id ='").append(userId).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getMapName())) {
            sql.append(" and map_name like '%").append(param.getMapName()).append("%'");
            countSql.append(" and map_name like '%").append(param.getMapName()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getUploadUserName())) {
            sql.append(" and upload_user_name like '%").append(param.getUploadUserName()).append("%'");
            countSql.append(" and upload_user_name like '%").append(param.getUploadUserName()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getStartTime()) && !PlatformObjectUtils.isEmpty(param.getEndTime())) {
            sql.append(" and upload_time between '").append(param.getStartTime()).append("' and '").append(param.getEndTime()).append("'");
            countSql.append(" and upload_time between '").append(param.getStartTime()).append("' and '").append(param.getEndTime()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getMapLevel1Category())) {
            sql.append(" and map_level1_category = '").append(param.getMapLevel1Category()).append("'");
            countSql.append(" and map_level1_category = '").append(param.getMapLevel1Category()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getMapLevel2Category())) {
            sql.append(" and map_level2_category = '").append(param.getMapLevel2Category()).append("'");
            countSql.append(" and map_level2_category = '").append(param.getMapLevel2Category()).append("'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getArea())) {
           if("全部".equals(param.getArea())){
                sql.append("and district = '").append(param.getProvince()).append(param.getCity()).append("全部'");
               countSql.append("and district = '").append(param.getProvince()).append(param.getCity()).append("全部'");
           }else{
               sql.append(" and (district like '%").append(param.getArea()).append("%' or district = '").append(param.getProvince()).append(param.getCity()).append("' or district='").append(param.getProvince()).append("')");
               countSql.append(" and (district like '%").append(param.getArea()).append("%' or district = '").append(param.getProvince()).append(param.getCity()).append("' or district='").append(param.getProvince()).append("')");
           }
        }else  if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
//            sql.append(" and (district like '%").append(param.getProvince()).append(param.getCity()).append("%' or district='").append(param.getProvince()).append("') ");
//            countSql.append(" and (district like '%").append(param.getProvince()).append(param.getCity()).append("%' or district='").append(param.getProvince()).append("') ");;

                sql.append(" and (district like '%").append(param.getProvince()).append(param.getCity()).append("%') ");
                countSql.append(" and (district like '%").append(param.getProvince()).append(param.getCity()).append("%' ) ");;

        }else if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            sql.append(" and district like '%").append(param.getProvince()).append("%'");
            countSql.append(" and district like '%").append(param.getProvince()).append("%'");
        }
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        String sqlWhole = sql.append(" order by upload_time desc ").append("  limit ").append(lastIndex).append(" offset ").append(firstIndex).toString();
        List<Map> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(Map.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        java.util.Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;

    }

    public int delete(List<String> dmIdList) {
        String sql = "update dzzh_map set del_flag='1',update_user_id='"+PlatformSessionContext.getUserID()+"',update_time=now() where dm_id in (:dmIdList)";
        java.util.Map map = new HashMap(2);
        map.put("dmIdList", dmIdList);
        return namedParameterJdbcTemplate.update(sql, map);
    }


    public List<MapAttachment> getMapAttachmentByCategory(List<String> categoryList){
         String sql = "SELECT m.map_level2_category,concat_ws('',attach.file_dir_id,attach.attach_path) as attachFullPath FROM dzzh_map m,sys_attachment_info attach where m.attachment_id=attach.attach_id and m.map_level1_category in (:category)  and del_flag='0' and attach.attach_id='a7f44bacc133427880f69d5c90cbc6c3'";
         java.util.Map idMap = new HashMap();
         idMap.put("category", categoryList);
         return namedParameterJdbcTemplate.query(sql.toString(), idMap, new BeanPropertyRowMapper<>(MapAttachment.class));
    }

    public int deleteSameBatch(String num, List<String> level2List,String mapLevel1Category,String mapLevel2Category) {
        String sql = "update dzzh_map set del_flag='1',update_user_id='"+PlatformSessionContext.getUserID()+"',update_time=now() where num=:num and map_level2_category in (:level2List) and map_level1_category=:mapLevel1Category and del_flag='0' and map_level2_category!=:mapLevel2Category";
        java.util.Map map = new HashMap(2);
        map.put("level2List", level2List);
        map.put("num", num);
        map.put("mapLevel1Category", mapLevel1Category);
        map.put("mapLevel2Category", mapLevel2Category);
        return namedParameterJdbcTemplate.update(sql, map);
    }

    public Map getById(String dmId) {
        String sql = "select * from dzzh_map where del_flag='0' and dm_id='"+dmId+"'";
        List<Map> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Map.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):null;
    }
}
